package com.aperos.webservices;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


public class AperosPasChersService {
	private String url = "jdbc:mysql://localhost/AperosPasChers";
	
	public Bar[] getAllBars() {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection conn = DriverManager.getConnection(url, "root", "pouerk");
			
			Statement st = conn.createStatement();
			
			ResultSet rsNb = st.executeQuery(
					"SELECT COUNT(*) FROM BAR"
			);
			int nbBars = 0;
			while (rsNb.next()) {
				nbBars = Integer.valueOf(rsNb.getString(1)).intValue();
			}
			Bar[] barList = new Bar[nbBars];
			

			ResultSet rs = st.executeQuery(
					"SELECT BAR_ID, BAR_LATITUDE_E6, BAR_LONGITUDE_E6, BAR_NAME, BAR_ADDRESS, BAR_PRICE_BEER, " +
					"BAR_PRICE_COCKTAIL, BAR_HAPPYHOUR_BEGIN_HOUR, BAR_HAPPYHOUR_BEGIN_MINUTE, BAR_HAPPYHOUR_END_HOUR, " +
					"BAR_HAPPYHOUR_END_MINUTE, BAR_PRICE_BEER_HAPPYHOUR, BAR_PRICE_COCKTAIL_HAPPYHOUR FROM BAR"
			);
			
			int cpt = 0;
			while (rs.next()) {
			    Integer id = Integer.valueOf(rs.getString(1));
			    Integer latitudeE6 = Integer.valueOf(rs.getString(2));
			    Integer longitudeE6 = Integer.valueOf(rs.getString(3));
			    String name = null;
			    if (rs.getString(4) != null) {
			    	name = rs.getString(4);
			    }
			    String address = null;
			    if (rs.getString(5) != null) {
			    	address = rs.getString(5);
			    }
			    Double priceBeer = null;
			    if (rs.getString(6) != null) {
			    	priceBeer = Double.valueOf(rs.getString(6));
			    }
			    Double priceCocktail = null;
			    if (rs.getString(7) != null) {
			    	priceCocktail = Double.valueOf(rs.getString(7));
			    }
				Integer happyHourBeginHour = null;
				if (rs.getString(8) != null) {
					happyHourBeginHour = Integer.valueOf(rs.getString(8));
			    }
				Integer happyHourBeginMinute = null;
				if (rs.getString(9) != null) {
					happyHourBeginMinute = Integer.valueOf(rs.getString(9));
			    }
				Integer happyHourEndHour = null;
				if (rs.getString(10) != null) {
					happyHourEndHour = Integer.valueOf(rs.getString(10));
			    }
				Integer happyHourEndMinute = null;
				if (rs.getString(11) != null) {
					happyHourEndMinute = Integer.valueOf(rs.getString(11));
			    }
				Double priceBeerHappyHour = null;
				if (rs.getString(12) != null) {
					priceBeerHappyHour = Double.valueOf(rs.getString(12));
			    }
				Double priceCocktailHappyHour = null;
				if (rs.getString(13) != null) {
					priceCocktailHappyHour = Double.valueOf(rs.getString(13));
			    }
			    
			    Bar bar = new Bar(id, latitudeE6, longitudeE6, name, address, priceBeer, priceCocktail, happyHourBeginHour, happyHourBeginMinute,
			    		happyHourEndHour, happyHourEndMinute, priceBeerHappyHour, priceCocktailHappyHour);
		    	
			    barList[cpt] = bar;
			    cpt++;
			}

			rs.close();
			st.close();
			conn.close();
	    	
	    	return barList;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
       
    }
	
	public String addBar(Integer latitudeE6, Integer longitudeE6, String name, String address, Double priceBeer, Double priceCocktail,
			Integer happyHourBeginHour, Integer happyHourBeginMinute, Integer happyHourEndHour, 
			Integer happyHourEndMinute, Double priceBeerHappyHour, Double priceCocktailHappyHour) {
		try {
			if (name == null || name.equals("")) {
				return "failed|noName";
			}
			
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection conn = DriverManager.getConnection(url, "root", "pouerk");
			
			Statement st = conn.createStatement();
			
			if (name != null) {
				name = "\"" + name + "\"";
			} else {
				name = null;
			}
			if (address != null && !address.equals("")) {
				address = "\"" + address + "\"";
			} else {
				address = null;
			}

			st.execute(
					"INSERT INTO BAR (BAR_LATITUDE_E6, BAR_LONGITUDE_E6, BAR_NAME, BAR_ADDRESS, BAR_PRICE_BEER, " +
					"BAR_PRICE_COCKTAIL, BAR_HAPPYHOUR_BEGIN_HOUR, BAR_HAPPYHOUR_BEGIN_MINUTE, BAR_HAPPYHOUR_END_HOUR, " +
					"BAR_HAPPYHOUR_END_MINUTE, BAR_PRICE_BEER_HAPPYHOUR, BAR_PRICE_COCKTAIL_HAPPYHOUR) " +
					"VALUES (" +
						latitudeE6 + ", " +
						longitudeE6 + ", " +
						name + ", " +
						address + ", " +
						priceBeer + ", " +
						priceCocktail + ", " +
						happyHourBeginHour + ", " +
						happyHourBeginMinute + ", " +
						happyHourEndHour + ", " +
						happyHourEndMinute + ", " +
						priceBeerHappyHour + ", " +
						priceCocktailHappyHour + ") "
			);
		} catch (Exception e) {
			e.printStackTrace();
			return "failed";
		}
        
		return "OK ";
	}
	

	public String editBar(Integer id, String name, String address, Double priceBeer, Double priceCocktail,
			Integer happyHourBeginHour, Integer happyHourBeginMinute, Integer happyHourEndHour, 
			Integer happyHourEndMinute, Double priceBeerHappyHour, Double priceCocktailHappyHour) {
		try {
			if (id == null) {
				return "failed|noId";
			}
			if (name == null || name.equals("")) {
				return "failed|noName";
			}
				
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection conn = DriverManager.getConnection(url, "root", "pouerk");
			
			Statement st = conn.createStatement();

			if (name != null) {
				name = "\"" + name + "\"";
			} else {
				name = null;
			}
			if (address != null && !address.equals("")) {
				address = "\"" + address + "\"";
			} else {
				address = null;
			}
			
			st.execute(
					"UPDATE BAR SET " +
						"BAR_NAME = " + name + ", " +
						"BAR_ADDRESS = " + address + ", " +
						"BAR_PRICE_BEER = " + priceBeer + ", " +
						"BAR_PRICE_COCKTAIL = " + priceCocktail + ", " +
						"BAR_HAPPYHOUR_BEGIN_HOUR = " + happyHourBeginHour + ", " +
						"BAR_HAPPYHOUR_BEGIN_MINUTE = " + happyHourBeginMinute + ", " +
						"BAR_HAPPYHOUR_END_HOUR = " + happyHourEndHour + ", " +
						"BAR_HAPPYHOUR_END_MINUTE = " + happyHourEndMinute + ", " +
						"BAR_PRICE_BEER_HAPPYHOUR = " + priceBeerHappyHour + ", " +
						"BAR_PRICE_COCKTAIL_HAPPYHOUR = " + priceCocktailHappyHour + " " +
					"WHERE BAR_ID = " + id
			);
			return "OK";
		} catch (Exception e) {
			e.printStackTrace();
			return "failed";
		}
		
	}
	
	public String addBarError(Integer barId, Integer refError, String barErrorMessage) {
		try {
			if (barId == null) {
				return "failed|noId";
			}
			if (refError == null) {
				return "failed|refError";
			}
				
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection conn = DriverManager.getConnection(url, "root", "pouerk");
			
			Statement st = conn.createStatement();

			if (barErrorMessage != null && !barErrorMessage.equals("")) {
				barErrorMessage = "\"" + barErrorMessage + "\"";
			} else {
				barErrorMessage = null;
			}
			
			st.execute(
					"INSERT INTO BAR_ERROR (BAR_ID, REF_BAR_ERROR, BAR_ERROR_MESSAGE) " +
					"VALUES (" +
						barId + ", " +
						refError + ", " +
						barErrorMessage + ") "
			);
			return "OK";
		} catch (Exception e) {
			e.printStackTrace();
			return "failed";
		}
		
	}
}
